﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spRest_DetermineLastBackup]
#-- Purpose:		Determine the latest backup_set_id for the given criteria
#--	Last Update:	05/10/2013
#--					For a complete history - please review comments in SVN
#-- Called By:		Restore Database Utility Executable
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spRest_DetermineLastBackup]
(
	@database_name			sysname,
	@restore_label			nvarchar(255)	= NULL
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE @backup_set_id int

--- Pre-process input parameters
IF LTRIM(RTRIM(@restore_label)) = ''
	SELECT	@restore_label = NULL

--- Determine the latest backup_set_id
SELECT		TOP 1 @backup_set_id = backup_set_id
FROM		[msdb].[dbo].[backupset]
WHERE		database_name = @database_name
			AND is_copy_only = 0
			AND (
				@restore_label IS NULL
				OR (
					name = @restore_label
					AND backup_start_date >= DATEADD(hour, -6, GETDATE())
				)
			)
ORDER BY	backup_start_date DESC

--- Return the backupset, 0 if nothing was found
SELECT	ISNULL(@backup_set_id, 0) last_backup_set_id

SET NOCOUNT OFF